# Excel导入处理器
from .utils import check_excel_file, read_xls_data, read_xls_by_cell, safe_float, normalize_japanese_prefecture
from .models import LocalOrder, TransferOrder, AfterSaleOrder
from django.utils import timezone
from datetime import datetime


class ExcelImportHandler:
    """Excel导入处理器基类"""
    
    @staticmethod
    def validate_file(file):
        """验证Excel文件"""
        is_valid, error_message = check_excel_file(file)
        if not is_valid:
            raise ValueError(error_message)
        return True


class LocalOrderImportHandler(ExcelImportHandler):
    """本地单Excel导入处理器"""
    
    @staticmethod
    def process_excel_import(file, user):
        ExcelImportHandler.validate_file(file)
        
        data_list = read_xls_data(file)
        success_count = error_count = skipped_count = 0
        error_records = []
        
        for row_data in data_list:
            try:
                result = LocalOrderImportHandler._process_single_row(row_data, user)
                if result == 'skipped':
                    skipped_count += 1
                else:
                    success_count += 1
            except Exception as e:
                fs_no = str(row_data.get('FS NO.', '')).strip() if row_data else '未知'
                error_records.append({'fs_no': fs_no, 'error': str(e)})
                error_count += 1
        
        return {
            'success_count': success_count,
            'error_count': error_count,
            'skipped_count': skipped_count,
            'error_records': error_records
        }
    
    @staticmethod
    def _process_single_row(row_data, user):
        """处理单行数据"""
        fs_no = str(row_data.get('FS NO.', '')).strip()
        if not fs_no:
            raise ValueError('FS编号不能为空')
        
        # 检查重复
        if LocalOrder.objects.filter(fs_no=fs_no).exists():
            raise ValueError('FS编号已存在')
        
        # 获取尺寸和重量
        weight = safe_float(row_data.get('Weight', 0))
        length = safe_float(row_data.get('Long', 0))
        width = safe_float(row_data.get('Width', 0))
        height = safe_float(row_data.get('Height', 0))
        
        if weight <= 0 or (length <= 0 and width <= 0 and height <= 0):
            return 'skipped'
        
        # 获取地址信息
        address_info = LocalOrderImportHandler._extract_address_info(row_data)
        
        # 创建订单
        order_data = {
            'fs_no': fs_no,
            'weight': weight,
            'length': length,
            'width': width,
            'height': height,
            'created_by': user,
            **address_info
        }
        
        LocalOrder.objects.create(**order_data)
        return 'success'
    
    @staticmethod
    def _extract_address_info(row_data):
        """提取地址信息"""
        state = str(row_data.get('State', '')).strip()
        city = str(row_data.get('City or Town', '')).strip()
        address1 = str(row_data.get('Address 1', '')).strip()
        address2 = str(row_data.get('Address 2', '')).strip()
        attention = str(row_data.get('Attention', '')).strip()
        company_name = str(row_data.get('Company or Name', '')).strip()
        
        # 标准化都道府县
        if any([state, city, address1, address2, attention, company_name]):
            state = normalize_japanese_prefecture(
                city_value=city, state_value=state, address1=address1,
                address2=address2, attention=attention, company_name=company_name
            )
        
        return {
            'state': state,
            'city': city,
            'address1': address1,
            'address2': address2,
            'attention': attention,
            'company_name': company_name,
            'phone_number': str(row_data.get('Phone number', '')).strip(),
            'postal_code': str(row_data.get('Postal Code:', '')).strip()
        }


class TransferOrderImportHandler(ExcelImportHandler):
    """转运单Excel导入处理器"""
    
    @staticmethod
    def process_excel_import(file, user):
        ExcelImportHandler.validate_file(file)
        
        data_list = read_xls_data(file)
        success_count = error_count = 0
        error_records = []
        
        current_order = None
        current_packages = []
        
        for row_data in data_list:
            try:
                result = TransferOrderImportHandler._process_single_row(
                    row_data, user, current_order, current_packages
                )
                
                if result['action'] == 'new_order':
                    # 保存之前的订单
                    if current_order:
                        TransferOrderImportHandler._save_order(current_order, current_packages)
                        success_count += 1
                    
                    # 开始新订单
                    current_order = result['order']
                    current_packages = result['packages']
                elif result['action'] == 'add_package':
                    current_packages.append(result['package'])
                    current_order['box_count'] = len(current_packages)
                    
            except Exception as e:
                cw_no = str(row_data.get('CW NO.', '')).strip() if row_data else '未知'
                error_records.append({'cw_no': cw_no, 'error': str(e)})
                error_count += 1
        
        # 保存最后一个订单
        if current_order:
            try:
                TransferOrderImportHandler._save_order(current_order, current_packages)
                success_count += 1
            except Exception as e:
                error_records.append({'cw_no': current_order.get('cw_no', '未知'), 'error': str(e)})
                error_count += 1
        
        return {
            'success_count': success_count,
            'error_count': error_count,
            'error_records': error_records
        }
    
    @staticmethod
    def _process_single_row(row_data, user, current_order, current_packages):
        """处理单行数据"""
        fs_no = str(row_data.get('FS NO.', '')).strip()
        cw_no = str(row_data.get('CW NO.', '')).strip()
        
        package_info = {
            'length': safe_float(row_data.get('Length', 0)),
            'width': safe_float(row_data.get('Width', 0)),
            'height': safe_float(row_data.get('Height', 0)),
            'lwh_sum': safe_float(row_data.get('LWH Sum', 0))
        }
        
        if fs_no and cw_no:
            # 新订单
            address_info = TransferOrderImportHandler._extract_address_info(row_data)
            
            order = {
                'fs_no': fs_no,
                'cw_no': cw_no,
                'weight': safe_float(row_data.get('Weight', 0)),
                'created_by': user,
                'box_count': 1,
                **address_info
            }
            
            return {'action': 'new_order', 'order': order, 'packages': [package_info]}
        else:
            # 添加包裹到当前订单
            return {'action': 'add_package', 'package': package_info}
    
    @staticmethod
    def _extract_address_info(row_data):
        """提取地址信息"""
        state = str(row_data.get('State', '')).strip()
        city = str(row_data.get('City or Town', '')).strip()
        address1 = str(row_data.get('Address 1', '')).strip()
        address2 = str(row_data.get('Address 2', '')).strip()
        attention = str(row_data.get('Attention', '')).strip()
        company_name = str(row_data.get('Company or Name', '')).strip()
        
        # 标准化都道府县
        if any([state, city, address1, address2, attention, company_name]):
            state = normalize_japanese_prefecture(
                city_value=city, state_value=state, address1=address1,
                address2=address2, attention=attention, company_name=company_name
            )
        
        return {
            'state': state,
            'city': city,
            'address1': address1,
            'address2': address2,
            'attention': attention,
            'company_name': company_name,
            'phone_number': str(row_data.get('Phone number', '')).strip(),
            'postal_code': str(row_data.get('Postal Code:', '')).strip(),
            'country': str(row_data.get('Country/Territory', '')).strip()
        }
    
    @staticmethod
    def _save_order(order, packages):
        """保存订单"""
        # 检查重复
        if TransferOrder.objects.filter(cw_no=order['cw_no']).exists():
            raise ValueError('CW编号已存在')
        
        # 找到最大包裹
        max_package = max(packages, key=lambda x: x['lwh_sum'])
        order.update({
            'length': max_package['length'],
            'width': max_package['width'],
            'height': max_package['height'],
            'box_count': len(packages)
        })
        
        TransferOrder.objects.create(**order)


class AfterSaleOrderImportHandler(ExcelImportHandler):
    """售后单Excel导入处理器"""
    
    @staticmethod
    def process_excel_import(file, user):
        ExcelImportHandler.validate_file(file)
        
        try:
            # 读取特定单元格
            cell_data = {
                'cg_no': read_xls_by_cell(file, 'B6'),
                'supplier': read_xls_by_cell(file, 'B7'),
                'cs_no': read_xls_by_cell(file, 'A9'),
                'fs_no': read_xls_by_cell(file, 'A10'),
                'cw_no': read_xls_by_cell(file, 'A11')
            }
        except ValueError as e:
            raise ValueError(f'读取Excel文件失败: {str(e)}')
        
        # 创建售后单
        aftersale_order = AfterSaleOrder.objects.create(
            cg_no=cell_data['cg_no'],
            supplier=cell_data['supplier'],
            cs_no=cell_data['cs_no'],
            fs_no=cell_data['fs_no'],
            cw_no=cell_data['cw_no'],
            created_by=user
        )
        
        return {
            'order_data': {
                'id': aftersale_order.id,
                'cs_no': aftersale_order.cs_no,
                'fs_no': aftersale_order.fs_no,
                'cw_no': aftersale_order.cw_no,
                'cg_no': aftersale_order.cg_no,
                'supplier': aftersale_order.supplier
            }
        }
